package projbd;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Stack;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;


public class OrderSizeNumSchemeXMLParser extends DefaultHandler {
    
    private static BigInteger MAX_VALUE = new BigInteger("999999999");
    private ArrayList<FileReader> files = new ArrayList<FileReader>();
    private Stack<OrderSizeNumSchemeNode> currentPath = new Stack<OrderSizeNumSchemeNode>();
    private BigInteger pk = BigInteger.ZERO;
    private Writer statementsFile;

    private static Connection connection = null;
       
    public OrderSizeNumSchemeXMLParser() {
        super();
    }

    @Override
    public void startDocument() throws SAXException {
        
        System.out.println("start document");
    }

    @Override
    public void endDocument() throws SAXException {
        
        System.out.println("end document");
    }

    @Override
    public void characters(char ch[], int start, int length) {
        
        String text = new String(ch, start, length);
        text = text.trim().replace("\n", " ").replace("'", "").replace("\"", "").replace("!", "").replace("\\", "");
        
        if(text == null || text.isEmpty())
            return;
        
        //System.out.println("charac: " + text);
        OrderSizeNumSchemeNode node = new OrderSizeNumSchemeNode();
        node.setValue(text);
        currentPath.push(node);
        
        try {
            createNodeInsertSQL("text");
        } catch (IOException ex) {
            Logger.getLogger(OrderSizeNumSchemeXMLParser.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        currentPath.pop();
    }
    
    @Override
    public void startElement(String uri, String name, String qName, Attributes atts) {
        
        //System.out.println("startElement: " + name);
        
        OrderSizeNumSchemeNode node = new OrderSizeNumSchemeNode();
        node.setValue(name);
        
        StringBuilder attributes = new StringBuilder();
        if (atts != null) {
            for (int j = 0; j < atts.getLength(); j++) {
                attributes.append(atts.getLocalName(j)).append("=").append(atts.getValue(j)).append(" ");
            }           
            node.setAttributes(attributes.toString());
        }
        
        currentPath.push(node);
        try {
            createNodeInsertSQL("tag");
        } catch (IOException ex) {
            Logger.getLogger(OrderSizeNumSchemeXMLParser.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public BigInteger getOrder(OrderSizeNumSchemeNode father) {
        
        BigInteger order;
        
        if(father.getCurrentSonOrder() != null) {
            order = father.getCurrentSonOrder().add(father.getCurrentSonSize()).add(BigInteger.ONE);
        } else {
            order = father.getSize().divide(new BigInteger("20")).add(father.getOrder());
            father.setFirstSonOrder(order);
        }
        
        return order;
    }
    
    public BigInteger getSize(OrderSizeNumSchemeNode father) {
        
        BigInteger size;
        
        if(father.getCurrentSonSize() != null) {

            BigInteger fatherSizeUsed = father.getCurrentSonOrder().subtract(father.getFirstSonOrder());
            fatherSizeUsed = fatherSizeUsed.add(father.getCurrentSonSize());
            BigInteger fatherSizeAvailable = father.getSize()/*.divide(new BigInteger("2"))*/.subtract(fatherSizeUsed);
            size = fatherSizeAvailable.divide(new BigInteger("20"));

        } else {
            size = father.getSize().divide(new BigInteger("20"));
        }
        
        return size;
    }
    
    public void createNodeInsertSQL(String datatype) throws IOException {
        /*
            id | parent_id | oder | size | value | attributes | datatype
        */
        StringBuilder sql = new StringBuilder();

        OrderSizeNumSchemeNode node = currentPath.lastElement();
        if(pk.equals(BigInteger.ZERO)) {
            sql.append(pk).append("!");
            sql.append(pk).append("!");
            sql.append(1).append("!");
            sql.append(MAX_VALUE).append("!");
            sql.append(node.getValue()).append("!");
            sql.append((node.getAttributes() == null) ? "#null" : node.getAttributes()).append("!");
            sql.append(datatype).append("\n");
            
            node.setPk(pk);
            node.setOrder(BigInteger.ONE);
            node.setSize(MAX_VALUE);
            
            pk = pk.add(BigInteger.ONE);

            statementsFile.write(sql.toString());
            //System.out.println(sql);
            return;
        }
        
        // ID
        sql.append(pk).append("!");
        
        OrderSizeNumSchemeNode father = currentPath.elementAt(currentPath.size()-2);

        // PARENT_ID
        String parent_id = father.getPk().toString();
        sql.append(parent_id).append("!");
        
        // ORDER
        BigInteger order = getOrder(father);
               
        // SIZE
        BigInteger size = getSize(father);

        father.setCurrentSonOrder(order);
        father.setCurrentSonSize(size);
        
        sql.append(order).append("!");
        sql.append(size).append("!");
        sql.append(node.getValue()).append("!");
        sql.append((node.getAttributes() == null) ? "#null" : node.getAttributes()).append("!");
        sql.append(datatype).append("\n");
        
        node.setPk(pk);
        node.setOrder(order);
        node.setSize(size);
        
        statementsFile.write(sql.toString());
        //System.out.println(sql);
        //runSQL(new String(sql));
        
        pk = pk.add(BigInteger.ONE);
    }
    
    public void runSQL(String sql) {
        
        try {
            Statement statement = connection.createStatement();
            statement.execute(sql);
            statement.executeBatch();
            
        } catch (SQLException ex) {
            Logger.getLogger(OrderSizeNumSchemeXMLParser.class.getName()).log(Level.SEVERE, null, ex);
        }     
    }
    
    @Override
    public void endElement(String uri, String name, String qName) {
        
        //System.out.println("endElement: " + name);
        
        OrderSizeNumSchemeNode node = this.currentPath.pop();
        
        if(! node.getValue().equals(name))
            System.out.println("OrderSizeNumScheme: error at endElement !");
    }
        
    public void addFile(String path) {
        
        FileReader file = null;
        
        if(path == null)
            return;
        
        try {
            
            file = new FileReader(path);
            
        } catch (FileNotFoundException ex) {
            System.out.println("Error creating FileReader of XML file !");
            Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            return;
        }
        
        files.add(file);
    }
    
    public void run() throws UnsupportedEncodingException, FileNotFoundException {

        statementsFile = new OutputStreamWriter(new FileOutputStream("C:\\bd\\statements_2.sql"), "UTF-8");
        XMLReader xr;
        
        try {
            xr = XMLReaderFactory.createXMLReader();        
        } catch (SAXException ex) {
            Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            return;
        }
        
        xr.setContentHandler(this);
	xr.setErrorHandler(this);

        for(FileReader file: files) {
            
            try {
                
                xr.parse(new InputSource(file));
                
            } catch (IOException ex) {
                Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SAXException ex) {
                Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            }
	}
        try {
            statementsFile.flush();
        } catch (IOException ex) {
            Logger.getLogger(OrderSizeNumSchemeXMLParser.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }
    
    public static void main(String[] args) throws SQLException, UnsupportedEncodingException, FileNotFoundException {
        
        /* BD Connection */
                
        try {           
            Class.forName("org.postgresql.Driver");         
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/projbd", "postgres", "srx3325");
            if(connection != null) {
                System.out.println("CONNECTED !!!");
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProjBD.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ProjBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        /* XML Parser */
        
        OrderSizeNumSchemeXMLParser parser = new OrderSizeNumSchemeXMLParser();    
        parser.addFile("input/hamlet.xml");   
        //parser.addFile("input/dblp.xml"); 
        parser.run();
           
        /* BD Close Connection */
        connection.close();      
    }
    
}
